'''
    @Author:管晨羽
    @Date:2023-5-30
    @Version:
    @Since:python3.11
    @See:
    @Description:工具类，实现基金的基本计算
    @Functions:
'''

import pandas as pd

#计算收益率的函数
def interest(df,funds_symbol):
    interest={}
    df_group = df.groupby('Symbol')
    for i in funds_symbol:
        group = df_group.get_group(int(i))
        group = group.sort_values(by=['TradingDate'])
        start = group.loc[group.index[0], 'ClosePrice']
        end = group.loc[group.index[-1], 'ClosePrice']
        if start != 0 and end !=0:
            returnrate = (end - start) / start
            interest[i]=returnrate
    return interest

#对数据归一化的函数
def zero_one(df,columns1,columns2):
    maxvalue=df[columns1].max()
    minvalue=df[columns1].min()
    df[columns2]=(df[columns1]-minvalue)/(maxvalue-minvalue)
    return df

#对于基金进行打分的函数
def score(df,rate1,rate2,rate3,rate4,rate5):
    df['rank'] = rate1 * df['Week_Standard'] + rate2 * df['Month_Standard'] + rate3 * df['Year_Standard'] + rate4 * df['Flow'] + rate5 * df['Sharpe']
    name = ['Symbol', 'FullName', 'Category', 'Rank']
    df_rd = df.sort_values(by=['rank'], ascending=False)
    df_rd = df_rd.loc[:, ('Symbol', 'FullName', 'Category', 'rank')]
    df_rd.columns = name
    return df_rd

#推荐基金的函数
def recomend(df,gupiao,zhaiquan):
    l = []
    count_gupiao = 0
    count_zhaiquan = 0
    for i in range(0, len(df)):
        if count_zhaiquan < zhaiquan:
            if df.iloc[i][2] != '股票型基金':
                count_zhaiquan += 1
                l.append(list(df.iloc[i]))
        if count_gupiao < gupiao:
            if df.iloc[i][2] == '股票型基金':
                count_gupiao += 1
                l.append(list(df.iloc[i]))
    l=pd.DataFrame(l,columns=['Symbol', 'FullName', 'Category', 'rank'])
    return l

funds=pd.read_excel('sharpe.xlsx')
funds_symbol=set(funds['Symbol'])
NAV=pd.read_excel('NAV.xlsx')

#筛选出在基金池里面的基金的净值数据
funds_NAV=NAV[NAV['Symbol'].isin(funds_symbol)]
funds_group=funds_NAV.groupby('Symbol')
funds_NAV=funds_NAV[(funds_NAV['TradingDate']>='2022-05-26')]

#筛选近一周、近一月、近一年的净值数据
funds_Recentweek=funds_NAV[(funds_NAV['TradingDate']>='2023-05-22')]
funds_Recentmonth=funds_NAV[(funds_NAV['TradingDate']>='2023-04-26')]
funds_Recentyear=funds_NAV[(funds_NAV['TradingDate']>='2022-05-26')]
interest_Recentweek=interest(funds_Recentweek,set(funds_Recentweek['Symbol']))
interest_Recentmonth=interest(funds_Recentmonth,set(funds_Recentmonth['Symbol']))
interest_Recentyear=interest(funds_Recentyear,set(funds_Recentyear['Symbol']))
funds_performance=[]

#计算出近一周、近一月、近一年的收益率数据
for i in funds_symbol:
    group=funds_group.get_group(int(i))
    if len(group)>100 and i in interest_Recentweek.keys() and i in interest_Recentmonth.keys() and i in interest_Recentyear.keys():
        funds_performance.append((i,funds[funds['Symbol']==i].iloc[0][1],funds[funds['Symbol']==i].iloc[0][2],interest_Recentweek[i],interest_Recentmonth[i],interest_Recentyear[i],funds[funds['Symbol']==i].iloc[0][4],funds[funds['Symbol']==i].iloc[0][5]))
funds_performance=pd.DataFrame(funds_performance,columns=['Symbol','FullName','Category','Returnrate_Recentweek','Returnrate_Recentmonth','Returnrate_Recentyear','Flow','Sharpe'])
funds_performance.to_excel('funds_performace.xlsx')
print(funds_performance)

'''基金打分权重
股票型基金占比
               近一周收益    近一月收益   近一年收益   近三月波动率   夏普比率
20               0.05        0.25       0.35       0.3         0.05
40               0.15        0.25       0.2       0.2         0.2  
60               0.2         0.4        0.1       0.1         0.2
80               0.25        0.5        0.05      0.05        0.15
'''

#对数据进行归一化
funds_performance=zero_one(funds_performance,'Returnrate_Recentweek','Week_Standard')
funds_performance=zero_one(funds_performance,'Returnrate_Recentmonth','Month_Standard')
funds_performance=zero_one(funds_performance,'Returnrate_Recentyear','Year_Standard')
funds_performance=zero_one(funds_performance,'Flow','Flow_Standard')
funds_performance=zero_one(funds_performance,'Sharpe','Sharpe_Standard')

#对基金根据投资者需要的股票型基金占比打分
funds20=score(funds_performance,0.05,0.25,0.35,0.3,0.05)
funds40=score(funds_performance,0.15,0.25,0.2,0.2,0.2)
funds60=score(funds_performance,0.2,0.4,0.1,0.1,0.2)
funds80=score(funds_performance,0.25,0.5,0.05,0.05,0.15)

#对基金进行推荐
recommend20=recomend(funds20,2,8)
recommend40=recomend(funds40,4,6)
recommend60=recomend(funds60,6,4)
recommend80=recomend(funds80,8,2)

#保存数据
recommend20.to_excel('股票类基金占20%的推荐基金.xlsx')
recommend40.to_excel('股票类基金占40%的推荐基金.xlsx')
recommend60.to_excel('股票类基金占60%的推荐基金.xlsx')
recommend80.to_excel('股票类基金占80%的推荐基金.xlsx')